package com.blue.jdbc.sql;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.blue.jdbc.annotation.CacheEntity;
import com.blue.jdbc.annotation.CacheVersion;
import com.blue.jdbc.annotation.GeneratorType;
import com.blue.jdbc.annotation.Parser;
import com.blue.jdbc.dialect.IDialect;

/**
 * SQL工具类
 * 
 * @author zhengj
 * @since 1.0 2011-11-29
 */
public class SqlUtil
{
	private static Logger log = LoggerFactory.getLogger(SqlUtil.class);
	private static String insertTpl = "insert into %s (%s) values (%s)";
	private static String updateTpl = "update %s set %s where %s";
	private static String deleteTpl = "delete from %s where %s";
	private static String selectTpl = "select * from %s where %s";

	private static SqlUtil sqlUtil = new SqlUtil();
	private IDialect dialect;
	private Parser parser = Parser.getInstance();

	private SqlUtil()
	{
	}

	public static SqlUtil getInstance()
	{
		return sqlUtil;
	}

	/**
	 * 产生默认的SQL语句
	 * 
	 * @param cacheEntity
	 */
	public void generateSQL(CacheEntity cacheEntity)
	{
		List<String> idKeyList = new ArrayList<String>();
		List<String> idValueList = new ArrayList<String>();
		List<String> insertKeyList = new ArrayList<String>();
		List<String>insertValueList = new ArrayList<String>();
		List<String> idList = new ArrayList<String>();
		List<String> idVersionList = new ArrayList<>();
		cacheEntity.getIdMap().forEach((k, v) ->
		{
			idKeyList.add(dialect.escape(v.getColumn()));
			idValueList.add(":" + v.getName());
			idList.add(dialect.escape(v.getColumn()) + "=:" + v.getName());
			idVersionList.add(dialect.escape(v.getColumn()) + "=:" + v.getName());
			
			if (v.getGeneratorType() != GeneratorType.INCREMENT)
			{
				insertKeyList.add(dialect.escape(v.getColumn()));
				insertValueList.add(":" + v.getName());
			}
		});
		
		List<String> columnKeyList = new ArrayList<String>();
		List<String> columnValueList = new ArrayList<String>();
		List<String> columnList = new ArrayList<String>();
		List<String> columnVersionList = new ArrayList<>();
		cacheEntity.getColumnMap().forEach((k, v) ->
		{
			columnKeyList.add(dialect.escape(v.getColumn()));
			columnValueList.add(":" + v.getName());
			columnList.add(dialect.escape(v.getColumn()) + "=:" + v.getName());
			columnVersionList.add(dialect.escape(v.getColumn()) + "=:" + v.getName());
			
			insertKeyList.add(dialect.escape(v.getColumn()));
			insertValueList.add(":" + v.getName());
		});
		
		// 版本
		CacheVersion version = cacheEntity.getVersion();
		if (version != null)
		{
			insertKeyList.add(dialect.escape(version.getColumn()));
			insertValueList.add(":" + version.getName());

			idVersionList.add(dialect.escape(version.getColumn()) + "=:" + version.getName());
			columnVersionList.add(dialect.escape(version.getColumn()) + "=" + dialect.escape(version.getColumn()) + "+1");
		}
		
		//产生插入SQL语句
		if (idKeyList.size() > 0)
		{
			String insertSQL = String.format(insertTpl, dialect.escape(cacheEntity.getTable()),
					StringUtils.join(insertKeyList, ','),
					StringUtils.join(insertValueList, ','));
			log.debug(cacheEntity.getClazz().getName() + " generate insert-sql: " + insertSQL);
			cacheEntity.setInsertSQL(insertSQL);
		}
		
		//产生更新SQL语句
		if (columnList.size() > 0)
		{
			String updateSQL = String.format(updateTpl, dialect.escape(cacheEntity.getTable()),
					StringUtils.join(columnList, ','), StringUtils.join(idList, " and "));
			log.debug(cacheEntity.getClazz().getName() + " generate update-sql: " + updateSQL);
			cacheEntity.setUpdateSQL(updateSQL);
			
			if (version != null)
			{
				String updateVersionSQL = String.format(updateTpl, dialect.escape(cacheEntity.getTable()),
						StringUtils.join(columnVersionList, ','), StringUtils.join(idVersionList, " and "));
				log.debug(cacheEntity.getClazz().getName() + " generate update-version-sql: " + updateVersionSQL);
				cacheEntity.setUpdateVersionSQL(updateVersionSQL);
			}
		}
		
		//产生删除SQL语句
		String deleteSQL = String.format(deleteTpl, dialect.escape(cacheEntity.getTable()),
				StringUtils.join(idList, " and "));
		log.debug(cacheEntity.getClazz().getName() + " generate delete-sql: " + deleteSQL);
		cacheEntity.setDeleteSQL(deleteSQL);
		
		if (idKeyList.size() == 1)
		{
			String idKey = idKeyList.get(0);
			
			//产生根据主键列表删除SQL语句
			String deleteListSQL = String.format(deleteTpl, dialect.escape(cacheEntity.getTable()), idKey + " in (%s)");
			log.debug(cacheEntity.getClazz().getName() + " generate delete-list-sql: " + deleteListSQL);
			cacheEntity.setDeleteListSQL(deleteListSQL);

			//产生根据主键列表查询SQL语句
			String getListSQL = String.format(selectTpl, dialect.escape(cacheEntity.getTable()), idKey + " in (%s)");
			log.debug(cacheEntity.getClazz().getName() + " generate get-list-sql: " + getListSQL);
			cacheEntity.setGetListSQL(getListSQL);
		}
		
		//产生查询SQL语句
		String selectSQL = String.format(selectTpl, dialect.escape(cacheEntity.getTable()),
				StringUtils.join(idList, "and "));
		log.debug(cacheEntity.getClazz().getName() + " generate get-sql: " + selectSQL);
		cacheEntity.setGetSQL(selectSQL);
		
	}
	
	/**
	 * 产生动态的 INSERT 语句
	 * 
	 * @param map 
	 * @param clazz
	 * @return
	 */
	public String insertSQL(Map<String, Object> map, Class<?> clazz)
	{
		CacheEntity cacheEntity = parser.parse(clazz);
		List<String> fieldList = new ArrayList<String>();
		List<String> columnList = new ArrayList<String>();
		cacheEntity.getIdMap().forEach((k, v) ->
		{
			if (v.getGeneratorType() == GeneratorType.UUID || v.getGeneratorType() == GeneratorType.ASSIGNED)
			{
				columnList.add(dialect.escape(v.getColumn()));
				fieldList.add(":" + v.getName());
			}
		});
		cacheEntity.getColumnMap().forEach((k, v) ->
		{
			Object val = map.get(v.getName());
			if (v.isMustInsert() || (val != null && !val.equals("")))
			{
				columnList.add(dialect.escape(v.getColumn()));
				fieldList.add(":" + v.getName());
			}
		});
		
		// 版本
		CacheVersion version = cacheEntity.getVersion();
		if (version != null)
		{
			columnList.add(dialect.escape(version.getColumn()));
			fieldList.add(":" + version.getName());
		}

		String sql = String.format(insertTpl, dialect.escape(cacheEntity.getTable()),
				StringUtils.join(columnList, ','), StringUtils.join(fieldList, ','));

		return sql;
	}

	/**
	 * 产生动态的 UPDATE 语句
	 * 
	 * @param map
	 * @param clazz
	 * @return
	 */
	public String updateSQL(Map<String, Object> map, Class<?> clazz)
	{
		CacheEntity cacheEntity = parser.parse(clazz);
		List<String> columnList = new ArrayList<String>();
		List<String> idList = new ArrayList<String>();
		cacheEntity.getColumnMap().forEach((k, v) ->
		{
			Object val = map.get(v.getName());
			if (v.isMustUpdate() || (val != null && !val.equals("")))
			{
				columnList.add(dialect.escape(v.getColumn()) + "=:" + v.getName());
			}
		});
		cacheEntity.getIdMap().forEach((k, v) ->
		{
			idList.add(dialect.escape(v.getColumn()) + "=:" + v.getName());
		});
		
		// 版本
		CacheVersion version = cacheEntity.getVersion();
		if (version != null)
		{
			columnList.add(dialect.escape(version.getColumn()) + "=" + dialect.escape(version.getColumn()) + "+1");
			Object val = map.get(version.getName());
			if (version.isForce() || val != null)
			{
				idList.add(dialect.escape(version.getColumn()) + "=:" + version.getName());
			}
		}

		String sql = String.format(updateTpl, dialect.escape(cacheEntity.getTable()),
				StringUtils.join(columnList, ','), StringUtils.join(idList, " and "));
		return sql;
	}

	public void setDialect(IDialect dialect)
	{
		this.dialect = dialect;
	}

}
